package com.libra.assemble.xutil.excel;

import com.libra.assemble.xutil.Constants;
import com.libra.assemble.xutil.FileContentType;
import com.libra.assemble.xutil.collection.compare.CellCompare;
import com.libra.assemble.xutil.excel.annotation.CellMapper;
import com.libra.assemble.xutil.excel.form.CellAttributeForm;
import com.google.common.collect.Lists;
import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.Closeable;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Type;
import java.math.BigDecimal;
import java.text.ParseException;
import java.util.*;

/**
 * Excel的操作处理工具类.
 *
 * @author Libra
 */
public final class ExcelUtils implements Closeable {

    /**
     * 日志记录
     */
    private static final Logger logger = LoggerFactory.getLogger(ExcelUtils.class);

    /**
     * 标题所在行
     */
    private int titleRowNumber;

    /**
     * 数据所在行
     */
    private int dataRowStartNumber;

    /**
     * 需要导入的表格
     */
    private Sheet sheet;

    /**
     * Workbook对象（这一个对象代表着对应的一个Excel文件）
     */
    private Workbook workbook;

    /**
     * 构造函数：初始化
     */
    public ExcelUtils() {
        this.titleRowNumber = 0;
        this.dataRowStartNumber = 1;
    }

    /**
     * 构造函数：初始化
     */
    public ExcelUtils(int titleRowNumber, int dataRowStartNumber) {
        this.titleRowNumber = titleRowNumber;
        this.dataRowStartNumber = dataRowStartNumber;
    }

    /**
     * 创建Excel工作簿对象.
     *
     * @param filePath
     * @throws FileNotFoundException
     * @throws IOException
     */
    public void setWorkbook(String filePath) throws IOException {
        if (StringUtils.isBlank(filePath)) {
            throw new IllegalArgumentException("Excel文件路径不能为空！");
        }

        // 根据后缀判断Excel文件的版本.
        if (filePath.trim().toLowerCase().endsWith("xls")) {
            setWorkbook(new HSSFWorkbook(new FileInputStream(filePath)));
        } else if (filePath.trim().toLowerCase().endsWith("xlsx")) {
            setWorkbook(new XSSFWorkbook(new FileInputStream(filePath)));
        } else {
            throw new IllegalArgumentException("不支持除：xls/xlsx以外的文件格式!!!");
        }
    }

    /**
     * 创建Excel工作簿对象.
     *
     * @param file
     * @throws FileNotFoundException
     * @throws IOException
     */
    public void setWorkbook(MultipartFile file) throws IOException {
        if (file.isEmpty()) {
            throw new IllegalArgumentException("Excel文件不能为空！");
        }

        logger.info("文件的内容类型 ContentType = {}", file.getContentType());

        // 根据后缀判断Excel文件的版本.
        if (file.getContentType().equals(FileContentType.xls)) {
            setWorkbook(new HSSFWorkbook(file.getInputStream()));
        } else if (file.getContentType().equals(FileContentType.xlsx)) {
            setWorkbook(new XSSFWorkbook(file.getInputStream()));
        } else {
            throw new IllegalArgumentException("不支持除：xls/xlsx以外的文件格式!!!");
        }
    }

    /**
     * 设置Excel工作簿对象.
     *
     * @param workbook
     */
    public void setWorkbook(Workbook workbook) {
        this.workbook = workbook;
        if (workbook.getNumberOfSheets() == 0) {
            this.sheet = workbook.createSheet();
        } else {
            this.sheet = workbook.getSheetAt(0);
        }

    }

    /**
     * 设置Excel工作簿对象.
     *
     * @param workbook
     */
    public void setWorkbook(Workbook workbook, String sheetName) {
        this.workbook = workbook;
        setSheet(sheetName);

    }

    /**
     * 创建/取得表格.
     *
     * @param sheetName
     */
    public void setSheet(String sheetName) {
        if (workbook.getSheetIndex(sheetName) > 0) {
            this.sheet = workbook.getSheet(sheetName);
        } else {
            this.sheet = workbook.createSheet(sheetName);
        }
    }

    /**
     * 设置导入的表格.
     *
     * @param index
     */
    public void setSheet(int index) {
        this.sheet = workbook.getSheetAt(index);
    }


    /**
     * Excel导入, 解析（读取 Excel 中所有的 Sheet 表格）.
     *
     * @param clazz
     * @return
     * @throws IllegalAccessException
     * @throws InstantiationException
     * @throws ParseException
     */
    public <T> List<T> parseAllSheet(Class<T> clazz) throws InstantiationException, IllegalAccessException, ParseException {
        List<T> resultList = Lists.newArrayList();
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            this.sheet = workbook.getSheetAt(i);
            resultList.addAll(parse(clazz));
        }

        return resultList;
    }


    /**
     * Excel导入, 解析.
     *
     * @param clazz
     * @return
     * @throws IllegalAccessException
     * @throws InstantiationException
     * @throws ParseException
     */
    public <T> List<T> parse(Class<T> clazz) throws InstantiationException, IllegalAccessException, ParseException {
        List<T> resultList = null;
        // 判断表格是否存在
        if (sheet != null) {
            // 初始化解析结果
            resultList = Lists.newArrayList();
            // 标题：字段
            Map<String, Field> fieldMap = new HashMap<String, Field>();
            // 列名：标题
            Map<String, String> titleMap = new HashMap<String, String>();

            // getFields()获得某个类的所有的公共（public）的字段，包括父类。
            // getDeclaredFields()获得某个类的所有申明的字段，即包括public、private和proteced，但是不包括父类的申明字段。
            // 同样类似的还有getConstructors()和getDeclaredConstructors()，getMethods()和getDeclaredMethods()。
            Field[] fields = clazz.getDeclaredFields();

            // 这里开始处理映射类型里的注解
            for (Field field : fields) {
                // 判断T类中字段是否使用了指定的注解
                if (field.isAnnotationPresent(CellMapper.class)) {
                    CellMapper cellMapper = field.getAnnotation(CellMapper.class);
                    // 标题与字段匹配 <code>@CellMapper(name = "名称", order = 0)</code>
                    fieldMap.put(cellMapper.name().trim(), field);
                }
            }

            // 取得Excel表格中的标题行.
            Row titleRow = sheet.getRow(this.titleRowNumber);
            if (titleRow != null) {
                // 这里开始解析匹配Excel的标题
                for (Cell title : titleRow) {
                    CellReference cellRef = new CellReference(title);
                    // cellRef.getCellRefParts() = [null, 行, 列] EX：[null, 1, E]（cellRef = E1）
                    // title.getRichStringCellValue() 取标题列值
                    //如果标题为数字格式，将标题转换为String(属于特殊情况)
                    if (title.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        titleMap.put(cellRef.getCellRefParts()[2], NumberToTextConverter.toText(title.getNumericCellValue()).trim());
                    } else {
                        titleMap.put(cellRef.getCellRefParts()[2], title.getRichStringCellValue().getString().trim());
                    }
                }


                // 解析Excel数据，匹配类T
                for (int i = this.dataRowStartNumber; i <= sheet.getLastRowNum(); i++) {
                    // 创建类实例.
                    T t = clazz.newInstance();
                    Row dataRow = sheet.getRow(i);
                    if (dataRow != null) {
                        for (Cell cell : dataRow) {
                            CellReference cellRef = new CellReference(cell);
                            // 取得数据所在列
                            String cellTag = cellRef.getCellRefParts()[2];
                            String name = titleMap.get(cellTag);
                            Field field = fieldMap.get(name);
                            // 如数据所在列的列名匹配，则取数据关联字段
                            if (null != field) {
                                // 设置字段可进入解析.
                                field.setAccessible(true);
                                getCellValue(cell, t, field);
                            }
                        }
                    }
                    resultList.add(t);
                }
            }

        }
        return resultList;
    }


    /**
     * 直接由存储过程创建Excel.
     *
     * @param list
     * @return
     */
    public Workbook create(List<Map<String, String>> list) {

        List<CellAttributeForm> cellList = Lists.newArrayList();
        for (String value : list.get(0).values()) {
            Gson gson = new Gson();
            Type type = new TypeToken<CellAttributeForm>() {
            }.getType();
            CellAttributeForm form = gson.fromJson(value, type);
            cellList.add(form);
        }

        Collections.sort(cellList, new CellCompare());
        //删除第一条标题行
        list.remove(0);


        if (!cellList.isEmpty() && !list.isEmpty()) {
            if (workbook == null) {
                setWorkbook(new HSSFWorkbook());
            }
            // 填充Excel表格的标题
            Row titleRow = sheet.createRow(titleRowNumber);
            for (int i = 0; i < cellList.size(); i++) {
                Cell cell = titleRow.createCell(i);
                CellAttributeForm attr = cellList.get(i);
                cell.setCellValue(attr.getName());
                if (attr.getWidth() != null) {
                    sheet.setColumnWidth(i, attr.getWidth() * 256);
                }

            }

            // 解析集合中的数据内容，填充Excel表格.
            for (int i = 0; i < list.size(); i++) {
                Map<String, String> valueMap = list.get(i);
                // 从数据行开始填写
                Row dataRow = sheet.createRow(i + dataRowStartNumber);
                for (int j = 0; j < cellList.size(); j++) {
                    Cell cell = dataRow.createCell(j);
                    CellAttributeForm attr = cellList.get(j);
                    if (valueMap.get(attr.getKey()) == null) {
                        //如果为空，设置默认值.
                        if (attr.getDefaults() != null) {
                            cell.setCellValue(attr.getDefaults());
                        } else {
                            cell.setCellValue("");
                        }
                    } else {
                        // 文本格式输入
                        cell.setCellValue(valueMap.get(attr.getKey()));
                    }

                }
            }

        }
        return workbook;
    }

    /**
     * Excel创建.
     *
     * @param <T>
     * @param list
     * @throws IllegalAccessException
     * @throws IllegalArgumentException
     */
    public <T> Workbook create(List<T> list, Class<T> clazz) throws IllegalArgumentException, IllegalAccessException {
        if (list != null && !list.isEmpty()) {
            if (workbook == null) {
                setWorkbook(new HSSFWorkbook());
            }

            // 标题：字段
            Map<String, Field> fieldMap = new HashMap<String, Field>();
            // 列数：标题
            Map<Integer, String> titleMap = new HashMap<Integer, String>();
            // 标题：宽度
            Map<String, Integer> widthMap = new HashMap<String, Integer>();

            // getFields()获得某个类的所有的公共（public）的字段，包括父类。
            // getDeclaredFields()获得某个类的所有申明的字段，即包括public、private和proteced，但是不包括父类的申明字段。
            // 同样类似的还有getConstructors()和getDeclaredConstructors()，getMethods()和getDeclaredMethods()。
            Field[] fields = clazz.getDeclaredFields();

            // 这里开始处理映射类型里的注解
            for (Field field : fields) {
                if (field.isAnnotationPresent(CellMapper.class)) {
                    CellMapper cellMapper = field.getAnnotation(CellMapper.class);
                    //判读字段是否在输出时“忽略”
                    if (!cellMapper.ignore()) {
                        // 注解与字段匹配 <code>@CellMapper(name = "名称", order = 0)</code>
                        fieldMap.put(cellMapper.name().trim(), field);
                        titleMap.put(cellMapper.order(), cellMapper.name().trim());
                        widthMap.put(cellMapper.name().trim(), cellMapper.columnWidth());
                    }
                }
            }

            // 按order排序，取得Excel的标题名.
            String[] values = titleMap.values().toArray(new String[]{});

            // 填充Excel表格的标题
            Row titleRow = sheet.createRow(titleRowNumber);
            for (int i = 0; i < values.length; i++) {
                Cell cell = titleRow.createCell(i);
                cell.setCellValue(values[i].trim());
                sheet.setColumnWidth(i, widthMap.get(values[i]) * 256);
            }

            // 解析集合中的数据内容，填充Excel表格.
            for (int i = 0; i < list.size(); i++) {
                // 从数据行开始填写
                Row dataRow = sheet.createRow(i + dataRowStartNumber);
                for (int j = 0; j < values.length; j++) {
                    Cell cell = dataRow.createCell(j);
                    Field field = fieldMap.get(values[j].trim());
                    if (field != null) {
                        // 设置字段可进入解析.
                        field.setAccessible(true);
                        setCellValue(cell, list.get(i), field);
                    }
                }
            }

        }
        return workbook;
    }

    /**
     * 取表格数据，匹配类T对应的字段field.
     *
     * @param cell
     * @param o
     * @param field
     * @throws IllegalAccessException
     * @throws ParseException
     */
    private void getCellValue(Cell cell, Object o, Field field) throws IllegalAccessException, ParseException {
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BLANK:
                field.set(o, null);
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                field.setBoolean(o, cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_ERROR:
                field.setByte(o, cell.getErrorCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                if (field.getType().isAssignableFrom(Double.class) || field.getType().getName().equals("double")) {
                    field.set(o, cell.getNumericCellValue());
                } else {
                    field.set(o, cell.getCellFormula());
                }
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    if (field.getType().getName().equals(Date.class.getName())) {
                        field.set(o, cell.getDateCellValue());
                    } else {
                        field.set(o, DateFormatUtils.format(
                                cell.getDateCellValue(),
                                field.getAnnotation(CellMapper.class).pattern()));
                    }
                } else {
                    if (field.getType().isAssignableFrom(Integer.class) || field.getType().getName().equals("int")) {
                        field.set(o, new Integer((int) cell.getNumericCellValue()));
                    } else if (field.getType().isAssignableFrom(Long.class) || field.getType().getName().equals("long")) {
                        field.set(o, new Long((long) cell.getNumericCellValue()));
                    } else if (field.getType().isAssignableFrom(Float.class) || field.getType().getName().equals("float")) {
                        field.set(o, new Float(cell.getNumericCellValue()));
                    } else if (field.getType().isAssignableFrom(Double.class) || field.getType().getName().equals("double")) {
                        field.set(o, new Double(cell.getNumericCellValue()));
                    } else if (field.getType().isAssignableFrom(String.class)) {
                        String s = String.valueOf(cell.getNumericCellValue());
                        if (s.contains("E")) {
                            s = s.trim();
                            BigDecimal bigDecimal = new BigDecimal(s);
                            s = bigDecimal.toPlainString();
                        }
                        //清除读取的数值（常规）中的.0
                        if (s.trim().endsWith(".0")) {
                            s = s.substring(0, s.trim().indexOf(".0"));
                        }
                        field.set(o, s);
                    } else {
                        field.set(o, cell.getNumericCellValue());
                    }
                }
                break;
            case Cell.CELL_TYPE_STRING:
                if (field.getType().getName().equals(Date.class.getName())) {
                    field.set(o, DateUtils.parseDate(cell.getRichStringCellValue().getString(), Constants.FORMAT_PATTERNS));
                } else if (field.getType().isAssignableFrom(Integer.class) || field.getType().getName().equals("int")) {
                    field.set(o, Integer.parseInt(cell.getStringCellValue()));
                } else if (field.getType().isAssignableFrom(Long.class) || field.getType().getName().equals("long")) {
                    field.set(o, Long.parseLong(cell.getStringCellValue()));
                } else if (field.getType().isAssignableFrom(Double.class) || field.getType().getName().equals("double")) {
                    field.set(o, Double.parseDouble(cell.getStringCellValue()));
                } else {
                    field.set(o, cell.getRichStringCellValue().getString());
                }
                break;
            default:
                field.set(o, cell.getStringCellValue());
                break;
        }
    }

    /**
     * 给表格赋值.
     *
     * @param cell
     * @param o
     * @param field
     * @throws IllegalArgumentException
     * @throws IllegalAccessException
     */
    private void setCellValue(Cell cell, Object o, Field field) throws IllegalArgumentException, IllegalAccessException {
        // 判断字段是否日期类型.
        if (field.get(o) == null) {
            cell.setCellValue("");
        } else if (field.getType().getName().equals(Date.class.getName())) {
            // 设置日期格式
            CellStyle dateCellStyle = workbook.createCellStyle();
            String pattern = field.getAnnotation(CellMapper.class).pattern();
            dateCellStyle.setDataFormat(workbook.createDataFormat().getFormat(pattern));
            // 输入日期值
            cell.setCellValue((Date) field.get(o));
            cell.setCellStyle(dateCellStyle);
        } else if (field.getType().isAssignableFrom(Double.class) || field.getType().getName().equals("double")) {
            cell.setCellValue((Double) field.get(o));
            CellStyle dateCellStyle = workbook.createCellStyle();
            DataFormat format = workbook.createDataFormat();
            String pattern = field.getAnnotation(CellMapper.class).pattern();
            dateCellStyle.setDataFormat(format.getFormat(pattern));
            cell.setCellStyle(dateCellStyle);
        } else {
            // 文本格式输入
            cell.setCellValue(field.get(o).toString());
        }
    }

    /**
     * 关闭打开的内容
     */
    @Override
    public void close() throws IOException {
        this.workbook.close();
    }

}
